<?php
namespace app\index\controller;

use think\Db;
use app\common\model\ToolModel;
use app\common\CommonController;
use app\index\model\UserModel;
class Plan extends CommonController
{
    public function getOldSkusInfo(){
    	set_time_limit(3600);
    	// ini_set('max_execution_time','0');
    	ini_set('memory_limit', '8866M');

    	$table = [];
		$table['category_skus'] = ['sku卍','category_id卍'];
		$table['price'] = ['sku卍','num卍reference_price'];
		$table['status_skuss'] = ['sku卍','status_id卍sales_status','old_status_id卍sales_status_old','reason卍remarks'];
		$table['weight'] = ['sku卍','num卍weight'];
		$table['asweight'] = ['sku卍','num卍baleweight'];
		$table['skus_volume'] = ['sku卍','volume卍bulk','var卍bulk_unit'];
		$table['length'] = ['sku卍','num卍aslength'];
		$table['width'] = ['sku卍','num卍aswide'];
		$table['height'] = ['sku卍','num卍ashight'];
		$table['aslength'] = ['sku卍','num卍balelength'];
		$table['aswidth'] = ['sku卍','num卍balewide'];
		$table['asheight'] = ['sku卍','num卍balehight'];
		$table['sku_editor'] = ['sku卍','accredit卍authorization','logo_describe卍logo'];
		// $table['skutransfer'] = ['sku卍','transfer卍developer'];
		$table['sku_eancode'] = ['sku卍','eancode卍'];
		$table['uptime'] = ['sku卍','uptime卍'];
		
    	$sql = "SELECT count(0) c FROM skus";
    	$count = ToolModel::dbSkusystemPDO($sql)??0;
    	$user = Db::connect('db_sso')->table('_of_sso_user')->where("nike!=''")->column('id','nike');
    	$skuuser = Db::connect('sku')->table('gleez.users')->where("nick!=''")->column('nick','id');
    	
    	// $sql = "SELECT id,nick FROM gleez.users WHERE id IN ({$user_id_str})";

    	// $skuArr = ['MR215900','R610'];
    	// WHERE s.sku IN ('".implode("','", $skuArr)."')
    	$q = 10000;
    	$allcount = 0;
    	$error = [];
    	for($i = 0;$i<=$count;$i+=$q){
    		$sql = "SELECT 
        			s.id,
        			s.sku,
        			s.isavailable `status`,
					s.date creation_time,
					s.modifytime modification_time,
					s.develop_num develop_code,
					s.develop_link,
					p.spu,
					m.color,
					m.type,
					m.shape,
					m.size,
					m.pattern
				FROM skus s 
				Left join spus p ON s.spu_id = p.spu_id
				Left join sku_multiplicity m ON s.id = m.sku_id
				ORDER BY s.id 
				LIMIT ".$i.",".$q."
				";
			$basics = ToolModel::dbSkusystemPDO($sql);

			//获取当前执行的SKU
			$skuArr = array_column($basics,'sku');
			$where = "s.sku IN ('".implode("','", $skuArr)."')";

			$sql = "SELECT 
						s.sku,
						y.`value` standard
					FROM skus_standard s 
					Left join skus_type y ON s.standard_id = y.id
					WHERE $where
					";
			$standard = ToolModel::dbSkusystemPDO($sql);

			$sql = "SELECT 
						s.sku,
						n.name
					FROM sku_cnname s 
					Left join cnname n ON s.attr_id = n.id 
					WHERE $where
					";
			$name = ToolModel::dbSkusystemPDO($sql);

			$sql = "SELECT 
						s.sku,
						r.standard qc_standard
					FROM sku_qc_standard s 
					Left join qc_standard r ON s.qc_standard_id= r.id
					WHERE $where
					";
			$qc_standard = ToolModel::dbSkusystemPDO($sql);

			$sql = "SELECT s.sku,s.serviceline,s.user_id developer FROM user_skus s WHERE s.buyertype = 1 AND $where";
			$serviceline = ToolModel::dbSkusystemPDO($sql);

			$sql = "SELECT s.sku,s.selling_points,s.description,s.specifications,s.package_included,s.notes,s.last_update_user,s.last_update_time FROM skus_ext s WHERE $where";
			$ext = ToolModel::dbSkusystemPDO($sql);

			$sql = "SELECT s.sku,s.attr_id,s.attr_name,s.attr_name_cn,s.opt_id,s.opt_name,s.opt_name_cn,s.opt_custom_name FROM smt_sku_attribute s WHERE $where";
			$smta = ToolModel::dbSkusystemPDO($sql,true);

			$sql = "SELECT s.sku,s.update_time,c.path FROM smt_attribute_management s LEFT JOIN smt_cate c ON s.classify_id = c.cate_id WHERE $where";
			$smtc = ToolModel::dbSkusystemPDO($sql);

			foreach ($table as $key => $val) {
				$field = '';
				foreach ($val as $v) {
					$str = explode('卍', $v);
					$field .= 's.'.$str[0].' '.$str[1].',';
				}
				$sql = ' SELECT '.substr($field,0,-1).' FROM '.$key.' s WHERE '.$where;
				$$key = ToolModel::dbSkusystemPDO($sql);
			}
			unset($skuArr,$where,$field,$sql);

			// $developer = array_column($skutransfer, 'developer'); //开发人员
   //      	$user_name = array_unique(array_filter($developer));
   //      	$user = UserModel::getUserByNike($user_name);

			// Db::startTrans();
	        try{
	        	$y = $z = 0;
	        	$skus = $attribute = $description = $smt = [];
	        	foreach ($basics as $sku => $val) {
	        		//检测SKU 命名不符合的SKU 不进行迁移
	        		if(!checkSku($sku)){
	        			$error[] = $sku;
	        			continue;
	        		} 

	        		$skus[$y]['sku'] = $sku;
	        		$skus[$y]['spu'] = $val['spu']??'';
	        		$skus[$y]['status'] = $val['status']??'';
					$skus[$y]['creation_time'] = $val['creation_time']??'';
	        		$skus[$y]['modification_time'] = $val['modification_time']??'';
	        		$skus[$y]['category_id'] = $category_skus[$sku]['category_id']??'';
	        		$skus[$y]['category_id'] = $skus[$y]['category_id']==''?0:$skus[$y]['category_id'];
	        		$skus[$y]['serviceline'] = $serviceline[$sku]['serviceline']??0;
	        		$skus[$y]['serviceline'] = $skus[$y]['serviceline']==''?0:(int)$skus[$y]['serviceline'];
	        		$skus[$y]['name'] = $name[$sku]['name']??'';
	        		$skus[$y]['name'] = mb_strlen($skus[$y]['name'])>100?mb_substr($skus[$y]['name'],0,100,'UTF-8'):$skus[$y]['name'];
	        		$skus[$y]['reference_price'] = $price[$sku]['reference_price']??'';
	        		$skus[$y]['reference_price'] = $skus[$y]['reference_price']==''?0:$skus[$y]['reference_price'];
	        		$skus[$y]['sales_status'] = $status_skuss[$sku]['sales_status']??'';
	        		$skus[$y]['sales_status'] = $skus[$y]['sales_status']==''?0:$skus[$y]['sales_status'];
	        		$skus[$y]['sales_status_old'] = $status_skuss[$sku]['sales_status_old']??'';
	        		$skus[$y]['sales_status_old'] = $skus[$y]['sales_status_old']==''?0:$skus[$y]['sales_status_old'];

	        		$attribute[$y]['sku'] = $sku;
					$attribute[$y]['asweight'] = $weight[$sku]['weight']??'';
					$attribute[$y]['asweight'] = $attribute[$y]['asweight']==''?0:$attribute[$y]['asweight'];
					$attribute[$y]['baleweight'] = $asweight[$sku]['baleweight']??'';
					$attribute[$y]['baleweight'] = $attribute[$y]['baleweight']==''?0:$attribute[$y]['baleweight'];
					$attribute[$y]['bulk'] = $skus_volume[$sku]['bulk']??'';
					$attribute[$y]['bulk_unit'] = $skus_volume[$sku]['bulk_unit']??'';
					$attribute[$y]['aslength'] = $length[$sku]['aslength']??'';
					$attribute[$y]['aslength'] = $attribute[$y]['aslength']==''?0:$attribute[$y]['aslength'];
					$attribute[$y]['aswide'] = $width[$sku]['aswide']??'';
					$attribute[$y]['aswide'] = $attribute[$y]['aswide']==''?0:$attribute[$y]['aswide'];
					$attribute[$y]['ashight'] = $height[$sku]['ashight']??'';
					$attribute[$y]['ashight'] = $attribute[$y]['ashight']==''?0:$attribute[$y]['ashight'];
					$attribute[$y]['balelength'] = $aslength[$sku]['balelength']??'';
					$attribute[$y]['balelength'] = $attribute[$y]['balelength']==''?0:$attribute[$y]['balelength'];
					$attribute[$y]['balewide'] = $aswidth[$sku]['balewide']??'';
					$attribute[$y]['balewide'] = $attribute[$y]['balewide']==''?0:$attribute[$y]['balewide'];
					$attribute[$y]['balehight'] = $asheight[$sku]['balehight']??'';
					$attribute[$y]['balehight'] = $attribute[$y]['balehight']==''?0:$attribute[$y]['balehight'];
					$attribute[$y]['color'] = $val['color']??'';
					$attribute[$y]['type'] = $val['type']??'';
					$attribute[$y]['shape'] = $val['shape']??'';
					$attribute[$y]['size'] = $val['size']??'';
					$attribute[$y]['pattern'] = $val['pattern']??'';
					$attribute[$y]['authorization'] = $sku_editor[$sku]['authorization']??'';
					$attribute[$y]['authorization'] = $attribute[$y]['authorization']=='是'?1:0;
					$attribute[$y]['logo'] = $sku_editor[$sku]['logo']??'';
					$attribute[$y]['developer'] = $serviceline[$sku]['developer']??'';
					!empty($attribute[$y]['developer']) && $attribute[$y]['developer'] = $skuuser[$serviceline[$sku]['developer']]??0;
					!empty($attribute[$y]['developer']) && $attribute[$y]['developer'] = $user[$attribute[$y]['developer']]??0;
					$attribute[$y]['develop_code'] = $val['develop_code']??'';
					$attribute[$y]['develop_link'] = $val['develop_link']??'';
					$attribute[$y]['standard'] = $standard[$sku]['standard']??'';
					$attribute[$y]['eancode'] = $sku_eancode[$sku]['eancode']??'';
					$attribute[$y]['qc_standard'] = $qc_standard[$sku]['qc_standard']??'';
					$attribute[$y]['qc_standard'] = mb_strlen($attribute[$y]['qc_standard'])>255?mb_substr($attribute[$y]['qc_standard'],0,255,'UTF-8'):$attribute[$y]['qc_standard'];
					$attribute[$y]['remarks'] = $status_skuss[$sku]['remarks']??'';
					$attribute[$y]['modification_operator'] = 0;
					$attribute[$y]['uptime'] = $uptime[$sku]['uptime']??'';

					$describe = [];
					$describe['Features'] = $ext[$sku]['selling_points']??'';
					$describe['Description'] = $ext[$sku]['description']??'';
					$describe['Specifications'] = $ext[$sku]['specifications']??'';
					$describe['Package Included'] = $ext[$sku]['package_included']??'';
					$describe['Notes'] = $ext[$sku]['notes']??'';

					$description[$y]['sku'] = $sku;
					$description[$y]['type'] = 1;
					$description[$y]['value_json'] = json_encode($describe);
					$description[$y]['operator'] = $ext[$sku]['last_update_user']??'';
					// $description[$y]['modification_time'] = $ext[$sku]['last_update_time']??;

					if(isset($smtc[$sku]) && isset($smta[$sku])){
						$smtv = [];
						$smtv['cateId'] = str_replace("_","卍",$smtc[$sku]['path']);
						foreach ($smta[$sku] as $s) {
							//其他
							if($s['opt_id'] == 4){
								$smtv['value'][$s['attr_id'].'_other'] = $s['opt_custom_name'];

								$smtv['smtKeyValue'][$s['attr_name_cn']] = $s['opt_custom_name'];

								$smtv['smtKeyValueEn'][$s['attr_name']] = $s['opt_custom_name'];

							}elseif($s['opt_id'] == 0){
								$smtv['value'][$s['attr_id']] = isset($smtv['value'][$s['attr_id']])?$smtv['value'][$s['attr_id']].'卍'.$s['opt_custom_name']:$s['opt_custom_name'];

								$smtv['smtKeyValue'][$s['attr_name_cn']] = isset($smtv['smtKeyValue'][$s['attr_name_cn']])?$smtv['smtKeyValue'][$s['attr_name_cn']].'卍'.$s['opt_custom_name']:$s['opt_custom_name'];

								$smtv['smtKeyValueEn'][$s['attr_name']] = isset($smtv['smtKeyValueEn'][$s['attr_name']])?$smtv['smtKeyValueEn'][$s['attr_name']].'卍'.$s['opt_custom_name']:$s['opt_custom_name'];

							}else{
								$smtv['value'][$s['attr_id']] = isset($smtv['value'][$s['attr_id']])?$smtv['value'][$s['attr_id']].'卍'.$s['opt_id']:$s['opt_id'];

								$smtv['smtKeyValue'][$s['attr_name_cn']] = isset($smtv['smtKeyValue'][$s['attr_name_cn']])?$smtv['smtKeyValue'][$s['attr_name_cn']].'卍'.$s['opt_name_cn']:$s['opt_name_cn'];

								$smtv['smtKeyValueEn'][$s['attr_name']] = isset($smtv['smtKeyValueEn'][$s['attr_name']])?$smtv['smtKeyValueEn'][$s['attr_name']].'卍'.$s['opt_name']:$s['opt_name'];

							}
						}

						$smt[$z]['sku'] = $sku;
						$smt[$z]['type'] = 3;
						$smt[$z]['value_json'] = json_encode($smtv);
						$smt[$z]['operator'] = $smtc[$sku]['operator']??'';
						$smt[$z]['modification_time'] = $smtc[$sku]['update_time']??'';
						$z++;
					}

					$y++;
					$allcount++;
        		}

	        	!empty($skus) && ToolModel::dbInsertOrUpdate('skus', $skus);
	        	!empty($attribute) && ToolModel::dbInsertOrUpdate('sku_attribute', $attribute);
	        	!empty($description) && ToolModel::dbInsertOrUpdate('sku_multiple_attribute', $description);
	        	!empty($smt) && ToolModel::dbInsertOrUpdate('sku_multiple_attribute', $smt);
	           	unset($skus,$attribute,$basics,$standard,$name,$qc_standard,$serviceline,$category_skus,$price,$status_skuss,$weight,$skus_volume,$length,$width,$height,$aslength,$aswidth,$asheight,$sku_editor,$skutransfer,$sku_eancode,$describe,$description,$ext);

	            // Db::commit(); 
	        } catch (\Exception $e) {
	        	// Db::rollback();
	        	print_r($skus);
	            echo $e->getMessage();exit;
	        }	
    	}

    	echo '成功了 '.$allcount.'条';
    	echo '失败SKU '.count($error).' --- '. implode(' , ', $error);
    	exit;

    	//分类表
    	//SELECT c.*,r.pare_id,r.max_id FROM categories c LEFT JOIN cate_relate r on r.cate_id = c.id ORDER BY c.id 
    }

    public function getOldSkusInfoDeveloper(){
    	set_time_limit(3600);
    	ini_set('memory_limit', '8866M');

    	$sql = "SELECT count(0) c FROM sku_attribute WHERE developer = 0";
    	$count = Db::query($sql)[0]['c'];

    	$user = Db::connect('db_sso')->table('_of_sso_user')->where("nike!=''")->column('id','nike');
    	$skuuser = Db::connect('skuuu')->table('gleez.users')->where("nick!=''")->column('nick','id');

    	$q = 10000;
    	$allcount = 0;
    	$error = [];
    	$skus = [];
    	$skusstr = '';
    	for($i = 0;$i<=$count;$i+=$q){
    		$sql = "SELECT sku FROM sku_attribute WHERE developer = 0 $skusstr ORDER BY id LIMIT 0,".$q;
			$basics = Db::query($sql);

			//获取当前执行的SKU
			$skuArr = array_column($basics,'sku');
			$where = "s.sku IN ('".implode("','", $skuArr)."')";

			$sql = "SELECT s.sku,s.user_id developer FROM user_skus s WHERE s.buyertype = 1 AND $where";
			$serviceline = ToolModel::dbSkusystemPDO($sql);

	        try{
	        	$y = 0;
	        	$attribute = [];
	        	foreach ($skuArr as $sku ) {
	        		$skus[]= $sku;
	        		$developer = $serviceline[$sku]['developer']??'';
	        		if($developer){
	        			$attribute[$y]['sku'] = $sku;
						$attribute[$y]['developer'] = $serviceline[$sku]['developer']??'';
						!empty($attribute[$y]['developer']) && $attribute[$y]['developer'] = $skuuser[$serviceline[$sku]['developer']]??0;
						!empty($attribute[$y]['developer']) && $attribute[$y]['developer'] = $user[$attribute[$y]['developer']]??0;
						$y++;
	        		}
        		}
        		// $skusstr = "AND sku NOT IN ('".implode("','", $skus)."')";
	        	!empty($attribute) && ToolModel::dbInsertOrUpdate('sku_attribute', $attribute);
	        	$allcount += count($attribute);
	        	unset($attribute,$basics,$serviceline);
	        } catch (\Exception $e) {
	        	print_r($attribute[$y]);
	            echo $e->getMessage();exit;
	        }	
    	}

    	echo '成功了 '.$allcount.'条';
    	exit;
    }
}
